package recommenRatings.data;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Date;
import java.util.ArrayList;
import java.util.List;

import recommenRatings.domain.Photo;
import recommenRatings.utils.UIDGenerator;

public class JDBCPhotoDAO implements IPhotoDAO {
/*
	public void insert(String photoPOID, Photo image) {
		Connection conn = ConnectionManager.getInstance().checkOut();
		PreparedStatement stmt = null;
		String sql = "INSERT INTO Photo (uploadDate,photoPOID,placePOID) VALUES (?,?,?)";
		try {
			stmt = conn.prepareStatement(sql);
			stmt.setDate(1,(Date)image.getUploadDate());
			stmt.setString(2, photoPOID);
			stml.setString(3,)

			stmt.executeUpdate();
		} catch (SQLException e) {
			System.out.println("Message: " + e.getMessage());
			System.out.println("SQLState: " + e.getSQLState());
			System.out.println("ErrorCode: " + e.getErrorCode());
		} finally {
			try {
				if (stmt != null)
					stmt.close();
			} catch (SQLException e) {
			}
		}
	}
*/    

	public void insert(Photo image) {
		Connection conn = ConnectionManager.getInstance().checkOut();
		String sql = "INSERT INTO Photo VALUES ( uploadDate, photo, photoPOID, placePOID ) (? ,? ,? ,? ) ";
		PreparedStatement stmt = null;
		try {
			Date photoDate = new Date(image.getUploadDate().getTime());
			stmt = conn.prepareStatement(sql);
			stmt.setDate(1,photoDate);
			stmt.setString(2,image.getPhoto());
			stmt.setString(3,UIDGenerator.getInstance().getKey());
			stmt.setString(4,"null");

			stmt.executeUpdate();
		} catch (SQLException e) {
			System.out.println("Message: " + e.getMessage());
			System.out.println("SQLState: " + e.getSQLState());
			System.out.println("ErrorCode: " + e.getErrorCode());
		} finally {
			try {
				if (stmt != null)
					stmt.close();
			} catch (SQLException e) {
			}
		}
		
	}
	
	public void insert(String image,String user) {
		Connection conn = ConnectionManager.getInstance().checkOut();
		PreparedStatement stmt = null;
		String sql = "INSERT INTO Photo (photoPOID,userPOID,photo) VALUES (?,?,?)";
		try {
			stmt = conn.prepareStatement(sql);
			stmt.setString(1,UIDGenerator.getInstance().getKey());
			stmt.setString(2,new JDBCUserDAO().selectUserOID(conn, user));
			stmt.setString(3,image);

			stmt.executeUpdate();
		} catch (SQLException e) {
			System.out.println("Message: " + e.getMessage());
			System.out.println("SQLState: " + e.getSQLState());
			System.out.println("ErrorCode: " + e.getErrorCode());
		} finally {
			try {
				if (stmt != null)
					stmt.close();
			} catch (SQLException e) {
			}
		}
		
	}
	
	public void delete(String photoPOID) {
		Connection conn = ConnectionManager.getInstance().checkOut();
		String sql = "DELETE FROM Photo WHERE (photoPOID = ?)";
		PreparedStatement stmt = null;

		try {
			stmt = conn.prepareStatement(sql);
			stmt.setString(1, photoPOID);

			stmt.executeUpdate();
		} catch (SQLException e) {
			System.out.println("Message: " + e.getMessage());
			System.out.println("SQLState: " + e.getSQLState());
			System.out.println("ErrorCode: " + e.getErrorCode());
		} finally {
			try {
				if (stmt != null)
					stmt.close();
			} catch (SQLException e) {
			}
		}
	}

	public Photo select(String photoPOID) {
		Connection conn = ConnectionManager.getInstance().checkOut();

		String sql = "SELECT * FROM Photo WHERE (photoPOID = ?)";
		PreparedStatement stmt = null;
		ResultSet result = null;
		Photo p = null;

		try {
			stmt = conn.prepareStatement(sql);
			stmt.setString(1, photoPOID);
			result = stmt.executeQuery();
			p = new Photo();
			p.setUploadDate(result.getDate("uploadDate"));
			p.setPhoto(result.getString("photo"));

		} catch (SQLException e) {
			System.out.println("Message: " + e.getMessage());
			System.out.println("SQLState: " + e.getSQLState());
			System.out.println("ErrorCode: " + e.getErrorCode());
		} finally {
			ConnectionManager.getInstance().checkIn(conn);
			try {
				if (result != null) {
					result.close();
				}
				if (stmt != null) {
					stmt.close();
				}
			} catch (SQLException e) {
			}
		}

		return p;
	}

	public List<Photo> selectAllPhoto( ) {
		Connection conn = ConnectionManager.getInstance().checkOut();

		PreparedStatement stmt = null;
		List<Photo> searchResults = new ArrayList<Photo>();
		ResultSet result = null;

		try {

			String sql = "SELECT * FROM Photo";
			stmt = conn.prepareStatement(sql);
			result = stmt.executeQuery();

			while (result.next()) {
				Photo p = new Photo();
				p.setUploadDate(result.getDate("uploadDate"));
				p.setPhoto(result.getString("photo"));

				searchResults.add(p);
			}
		} catch (SQLException e) {
			System.out.println("Message: " + e.getMessage());
			System.out.println("SQLState: " + e.getSQLState());
			System.out.println("ErrorCode: " + e.getErrorCode());
		} finally {
			ConnectionManager.getInstance().checkIn(conn);
			try {
				if (result != null)
					result.close();
				if (stmt != null)
					stmt.close();
			} catch (SQLException e) {
			}

		}
		return searchResults;
	}

	public String selectPOID (String photo){
		  Connection conn = ConnectionManager.getInstance().checkOut();
		  String sql = "SELECT * FROM Photo WHERE (photo = ?)";
	      PreparedStatement stmt = null;
	      ResultSet result = null;
	      String photoPoid=null;
	      
	      try {
				stmt = conn.prepareStatement(sql);
				stmt.setString(1, photo);
				result = stmt.executeQuery();
				photoPoid= new String();
				photoPoid=result.getString("photoPOID");

			} catch (SQLException e) {
				System.out.println("Message: " + e.getMessage());
				System.out.println("SQLState: " + e.getSQLState());
				System.out.println("ErrorCode: " + e.getErrorCode());
			} finally {
				ConnectionManager.getInstance().checkIn(conn);
				try {
					if (result != null) {
						result.close();
					}
					if (stmt != null) {
						stmt.close();
					}
				} catch (SQLException e) {
				}
			}

			return photoPoid;
		}
	      
	public List<Photo> select(Connection conn, String placeOID) {

		String sql = "SELECT * FROM Photo WHERE (placePOID = ? ) ";
		PreparedStatement stmt = null;
		ResultSet result = null;
		List<Photo> searchResults = new ArrayList<Photo>();

		try {
			stmt = conn.prepareStatement(sql);
			stmt.setString(1, placeOID);

			result = stmt.executeQuery();

			while (result.next()) {
				Photo p = null;
				p = new Photo();
				p.setPhoto(result.getString("photo"));
				p.setUploadDate(result.getDate("uploadDate"));

				searchResults.add(p);

			}

		} catch (SQLException e) {
			System.out.println("Message: " + e.getMessage());
			System.out.println("SQLState: " + e.getSQLState());
			System.out.println("ErrorCode: " + e.getErrorCode());
		} finally {

			try {
				if (result != null) {
					result.close();
				}
				if (stmt != null) {
					stmt.close();
				}
			} catch (SQLException e) {
			}
		}

		return searchResults;
	}

}
